package com.zfwhub.tutorial.jdbc.dao.impl;

import java.sql.*;

import com.zfwhub.tutorial.jdbc.base.JdbcUtils;
import com.zfwhub.tutorial.jdbc.dao.DaoException;
import com.zfwhub.tutorial.jdbc.dao.UserDao;
import com.zfwhub.tutorial.jdbc.domain.User;

public class UserDaoJdbcImpl implements UserDao {

    public void addUser(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into user(name,birthday, money) values (?,?,?) ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
            ps.setFloat(3, user.getMoney());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DaoException(e.getMessage(), e);
        } finally {
            JdbcUtils.free(rs, ps, conn);
        }
    }

    public void delete(User user) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();
            String sql = "delete from user where id=" + user.getId();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            throw new DaoException(e.getMessage(), e);
        } finally {
            JdbcUtils.free(rs, st, conn);
        }

    }

    public User findUser(String loginName, String password) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id, name, money, birthday  from user where name=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, loginName);
            rs = ps.executeQuery();
            while (rs.next()) {
                user = mappingUser(rs);
            }
        } catch (SQLException e) {
            throw new DaoException(e.getMessage(), e);
        } finally {
            JdbcUtils.free(rs, ps, conn);
        }
        return user;
    }

    public User getUser(int userId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id, name, money, birthday  from user where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userId);
            rs = ps.executeQuery();
            while (rs.next()) {
                user = mappingUser(rs);
            }
        } catch (SQLException e) {
            throw new DaoException(e.getMessage(), e);
        } finally {
            JdbcUtils.free(rs, ps, conn);
        }
        return user;
    }

    private User mappingUser(ResultSet rs) throws SQLException {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setMoney(rs.getFloat("money"));
        user.setBirthday(rs.getDate("birthday"));
        return user;
    }

    public void update(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "update user set name=?, birthday=?, money=? where id=? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
            ps.setFloat(3, user.getMoney());
            ps.setInt(4, user.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DaoException(e.getMessage(), e);
        } finally {
            JdbcUtils.free(rs, ps, conn);
        }
    }

}